package com.digitaleye.dpslite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TipsDBHelper extends SQLiteOpenHelper{
	
	static final String tag = "TipsDBHelper";
	
	SQLiteDatabase db;
	
	int tableSize = 0;
	
	/**********************************************************************
	 * Database Variables
	 */
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "DownPaymentSaver.db";
    /********************************************************************/
    
    public static final String TABLE_NAME = "Tips";
    
    public static final String COLUMN_TIP_ID = "_id";
    public static final String COLUMN_TIP_TITLE = "title";
    public static final String COLUMN_TIP_TEXT = "text";
    
    public static final String CREATE_TIPS_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
    		"(" +
    		COLUMN_TIP_ID + " INTEGER PRIMARY KEY, " +
    		COLUMN_TIP_TITLE + " VARCHAR(80), "+
    		COLUMN_TIP_TEXT + " TEXT"+
    		");";
    		
    
    
    public void createTable(){
    	this.db = getWritableDatabase();
    	
    	
    	this.db.execSQL(CREATE_TIPS_TABLE);
    	
    	this.db.close();
    	
    	
    	if(tableIsEmpty()) initTipsTable();
    	
    	
    }

	public TipsDBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
		createTable();
		
		this.tableSize = getTableSize();
		
		
		
	}

	
	/**
	 * retrieve a Tip object corresponding to the given ID
	 * @param id the tip ID
	 * @return
	 * @throws Exception if the cursor has no results, throws exception
	 */
	public Tip getTip(int id){
		
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_TIP_ID + " = ?";
		
		this.db = getReadableDatabase();
		
		Cursor result = this.db.rawQuery(query, new String[] {String.valueOf(id)});
		
		Tip tip = null;
		try {
			
			Log.e("tip ID =", " tip id = " + id);
			tip = new Tip(result);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		result.close();
		this.db.close();
		
		return tip;
		
	}
	
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	
	public void initTipsTable(){
		
		addTip("Savings Tip:", "<b>Automate your savings.</b> With regular contributes over time your savings build up quickly. Automatic deposits make it easy to stay on track.");
		
		
		addTip("Savings Tip:", "<b>Try the 30 day rule.</b> Whenever you're tempted to make an unnecessary purchase, wait 30 days and then see if you still feel as strongly about it.");
		
		addTip("Home Buying Tip:", "<b>Shop for your mortgage.</b> By comparing interest rates, application fees and other costs, you can save a considerable amount of money.");
		
		addTip("Mortgage Loan Tip:", "<b>With an adjustable rate mortgage (ARM)</b> your payments go up when interest rates rise. A fixed rate will keep your monthly payments the same for the term (usually 30 years.)");
	
		addTip("Savings Tip:", "<b>Instead of going to the mall</b>, go for a hike or a bike ride. Instead of going to a restaurant, have a candle-lit dinner at home.");
		
		addTip("Savings Tip:", "<b>Bag it.</b> You can save over $600 a year if you take a bagged lunch to work rather than buying lunch each day.");
		
		addTip("Savings Tip:", "<b>If you smoke, quit now!</b> You already know that you're waisting your money as well as damaging your health.");
		
		addTip("Mortgage Tip:", "<b>Avoid the PMI.</b> If you put down 20% or more you can avoid paying for private mortgage insurance.");
		
		addTip("Savings Tip:", "<b>Go into \"saving mode.\" </b>If you keep your savings goal a priority, and continually look for ways to reduce expenses, you might be amazed at your results.");
		
		addTip("Savings Tip:", "<b>Try a \"stay-cation.\"</b> Instead of taking a vacation, relax at home, take day-trips, or visit with friends or family. Better yet, earn a few extra dollars if you can.");
		
		addTip("Savings Tip:", "<b>Avoid lottery tickets.</b> The odds are against you, so don't start thinking there's an instant solution. It will almost always put you further from your goal. Save that dollar.");
		
		addTip("Savings Tip:", "<b>Don't look!</b> Avoid browsing catalogs, retail websites or walking through stores. These are all designed to tempt you to spend your money.");
		
		addTip("Savings Tip:", "<b>When you have to buy an item</b>, do your research. You can often find lower prices online for tires, eyeglasses, diapers and electronics, but stores can be lower on some items.");
		
		addTip("Savings Tip:", "<b>Make a grocery list.</b> Plan your meals and stick to your list at the grocery store. You can save a lot of money by avoiding the extra items you don't need.");
		
		addTip("Home Buying Tip:", "<b>Find out about government programs,</b> mortgage assistance, free mortgage counseling and other helpful information right here.");
		
	
	}
	
	public boolean tableIsEmpty(){
		
		this.db = getReadableDatabase();
		
		Cursor result = this.db.rawQuery("SELECT Count(*) FROM " + TABLE_NAME, null);
		
		result.moveToFirst();
		
		int count = result.getInt(0);
		
		result.close();
		this.db.close();
		
		if(count > 0) return false;
		else return true;
		
	}
	
	public int getTableSize(){
		
		this.db = getReadableDatabase();
		
		Cursor result = this.db.rawQuery("SELECT Count(*) FROM " + TABLE_NAME, null);
		
		result.moveToFirst();
		
		int count = result.getInt(0);
		
		result.close();
		this.db.close();
		
		return count;
		
	}
	
	public void addTip(String title, String text){
		
		this.db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_TIP_TEXT, text);
		values.put(COLUMN_TIP_TITLE, title);
		
		this.db.insert(TABLE_NAME, null, values);
		
		
	}
	
	public Tip getTip(){
		
		SQLiteDatabase db = getReadableDatabase();
		
		
		int id = (int) (this.tableSize*Math.random())+1;
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_TIP_ID + " = ?";
		
		Cursor result = db.rawQuery(query, new String[]{ String.valueOf(id)});
		
		Tip tip = null;
		try {
			tip = new Tip(result);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		result.close();
		db.close();
		
		return tip;
		
	}
	
}
